﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DB.SchemaSync.Library.Models;

namespace DB.SchemaSync.Library
{
	public partial class SchemaComparison
	{
		/// <summary>
		/// Required for seed data merge, enables the SchemaComparison to create an IDbCommand object when checking for target row existence
		/// </summary>
		public Func<IDbConnection, string, IDbCommand> CommandBuilderMethod { get; set; }

		public Func<IDbCommand, DataColumn, IDbDataParameter> ParameterBuilderMethod { get; set; }
		public Action<IDbDataParameter, DataRow> AssignParamValueAction { get; set; }

		public void MergeSeedData(SqlSyntax syntax, IDbConnection connection)
		{
			if (Source.SeedData == null) throw new InvalidOperationException("The source Database must have its SeedData property set first.");
			if (CommandBuilderMethod == null) throw new InvalidOperationException("To merge seed data, the SchemaComparison object requires the CommandBuilderMethod to be set.");
			if (ParameterBuilderMethod == null) throw new InvalidOperationException("To merge seed data, the SchemaComparison object requires the ParameterBuilderMethod to be set.");
			if (AssignParamValueAction == null) throw new InvalidOperationException("To merge seed data, the SchemaComparison object requires the AssignParamValueAction property to be set.");

			if (connection.State == ConnectionState.Closed) connection.Open();

			SeedData = GenerateInsertCommands(syntax, connection).ToLookup(row => row.TableName, row => row.CommandText);
		}

		private IEnumerable<SeedDataInsert> GenerateInsertCommands(SqlSyntax syntax, IDbConnection connection)
		{
			List<SeedDataInsert> results = new List<SeedDataInsert>();
			foreach (DataTable table in Source.SeedData.Tables)
			{
				using (var existenceCheck = TargetRowExistsQuery(syntax, connection, table))
				{
					foreach (DataRow row in table.Rows)
					{
						if (!TargetRowExists(connection, existenceCheck, row))
						{
							results.Add(new SeedDataInsert() { TableName = table.TableName, CommandText = InsertCommand(syntax, table, row) });
						}
					}
				}
			}
			return results;
		}

		private IDbCommand TargetRowExistsQuery(SqlSyntax syntax, IDbConnection connection, DataTable table)
		{
			if (!table.PrimaryKey.Any()) throw new InvalidOperationException($"DataTable {table.TableName} requires a primary key for seed data merge.");

			string whereClause = string.Join(" AND ", table.PrimaryKey.Select(col => $"<{col.ColumnName}>=@{col.ColumnName}"));

			string query = syntax.ApplyDelimiters($"SELECT 1 FROM <{table.TableName}> WHERE {whereClause}");

			IDbCommand result = CommandBuilderMethod.Invoke(connection, query);

			foreach (DataColumn col in table.PrimaryKey)
			{
				var param = ParameterBuilderMethod.Invoke(result, col);
				result.Parameters.Add(param);
			}

			return result;
		}

		private bool TargetRowExists(IDbConnection connection, IDbCommand query, DataRow row)
		{
			try
			{
				foreach (IDbDataParameter param in query.Parameters)
				{
					AssignParamValueAction.Invoke(param, row);
				}

				object result = query.ExecuteScalar();
				return (result?.Equals(1) ?? false);
			}
			catch
			{
				return false;
			}
		}

		private string InsertCommand(SqlSyntax syntax, DataTable table, DataRow row)
		{
			var columns = table.Columns.Cast<DataColumn>();
			string columnList = string.Join(", ", columns.Select(col => $"<{col.ColumnName}>"));
			string valueList = string.Join(", ", columns.Select(col => ColumnValue(col, row)));
			string result = syntax.ApplyDelimiters($"INSERT INTO <{table.TableName}> ({columnList}) VALUES ({valueList})");

			return result;
		}

		private string ColumnValue(DataColumn col, DataRow row)
		{
			return (row.IsNull(col)) ? "NULL" :
				(col.DataType.Equals(typeof(string))) ? FormatString(row[col].ToString()) :
				(col.DataType.Equals(typeof(DateTime))) ? FormatDate(Convert.ToDateTime(row[col])) :
				(col.DataType.Equals(typeof(bool))) ? FormatBool(Convert.ToBoolean(row[col])) :
				row[col].ToString();
		}

		private string FormatBool(bool value)
		{
			return (value) ? "1" : "0";
		}

		private string FormatDate(DateTime dateValue)
		{
			return "'" + dateValue.ToString("yyyy/MM/dd") + "'";
		}

		private string FormatString(string input)
		{
			return "'" + input.Replace("'", "''") + "'";
		}
	}
}